import asyncio
import pandas as pd
from datetime import datetime, timedelta
import json

from api_utils.stock_sales_api import StockData,SalesData
from api_utils.base_data_api import BaseDate
from database.table_data.mysql_to_df import get_select_country_store_id
from database.table_data.df_to_mysql import save_df_to_mysql_stock_sales



class GetStockSalesData:
    def __init__(self):
        base_data = BaseDate()
        self.sd_stock= StockData(base_data)
        self.sd_sales= SalesData(base_data)

    def get_fba_stock_data_source(self,sid):
        """FBA库存源表"""
        response = asyncio.run(self.sd_stock.get_fba_stock_source(sid))
        if response.message == "success":
            return response.data
        return None
    
    def get_fba_stock_data_lingxing(self,sid):
        """FBA库存领星"""
        response = asyncio.run(self.sd_stock.get_fba_stock_lingxing(sid))
        if response.message == "success":
            return response.data['list']
        return None
    
    def get_yuliu_stock_data_source(self,sid):
        """预留库存源表"""
        response = asyncio.run(self.sd_stock.get_yuliu_stock_source(sid))
        if response.message == "success":
            return response.data
        return None
    
    def get_awd_stock_data_lingxing(self,storehouse_id):
        """AWD库存领星"""
        response = asyncio.run(self.sd_stock.get_awd_stock_lingxing(storehouse_id))
        if response.message == "success":
            return response.data['list']
        return None
    
    def get_storehouse_id_data(self):
        """获取AWD仓id"""
        response = asyncio.run(self.sd_stock.get_storehouse_id())
        if response.message == "success":
            storehouse_id_list = [item['wid'] for item in response.data]
            storehouse_id = ",".join(str(wid) for wid in storehouse_id_list)
            return storehouse_id
        return None
    
    def get_30day_sales_data(self,sid,start_date,end_date):
        """获取近30日销量数据

        Args:
            sid: 店铺id
            start_date: 开始日期
            end_date: 结束日期
        """
        response = asyncio.run(self.sd_sales.get_30day_sales(sid,start_date,end_date))
        if response.message == "success":
            return response.data
        return None
    
    def get_fba_shipment_data(self,sid,start_date,end_date):
        """获取FBA货件数据

        Args:
            sid: 店铺id
            start_date: 开始日期(这里指创货件的日期范围，一般是发货计划表完成的那几天)
            end_date: 结束日期
        """
        response = asyncio.run(self.sd_stock.get_fba_shipment(sid,start_date,end_date))
        if response.message == "success":
            if len(response.data)>0:
                return response.data['list']
            else:
                return None
        return None


class ProcessSaveData():
    def __init__(self):
        self.store_id_df=get_select_country_store_id()
        print(self.store_id_df)
        self.gssd = GetStockSalesData()
        today_datetime = datetime.now()
        self.today_date = today_datetime.strftime('%Y-%m-%d')
        ago_30day_datetime = today_datetime - timedelta(days=29)
        self.ago_30day = ago_30day_datetime.strftime('%Y-%m-%d')
    
    def get_save_fba_stock_data_source(self,country,sid):
        """FBA库存源表"""
        data = self.gssd.get_fba_stock_data_source(sid)
        if data:
            print(f'{sid}-{country}FBA库存源表获取成功')
            df = pd.DataFrame(data)
            df['country'] = country
            df['get_date'] = self.today_date
            # 保存至数据库
            save_df_to_mysql_stock_sales(df=df,table_name='fba_stock_source')
    
    def get_save_fba_stock_data_lingxing(self,country,sid):
        """FBA库存领星表"""
        data = self.gssd.get_fba_stock_data_lingxing(sid)
        if data:
            print(f'{sid}-{country}FBA库存领星表获取成功')
            df = pd.DataFrame(data)
            json_columns = ['category_Arr', 'asin_principal_list', 'attribute','afn_fulfillable_quantity_multi']
            df = self._preprocess_dataframe(df,json_columns)
            df['country'] = country
            df['get_date'] = self.today_date
            # 保存至数据库
            save_df_to_mysql_stock_sales(df=df,table_name='fba_stock_lingxing')
    
    def get_save_yuliu_stock_data_source(self,country,sid):
        """预留库存源表"""
        data = self.gssd.get_yuliu_stock_data_source(sid)
        if data:
            print(f'{sid}-{country}预留库存源表获取成功')
            df = pd.DataFrame(data)
            df['country'] = country
            df['get_date'] = self.today_date
            # 保存至数据库
            save_df_to_mysql_stock_sales(df=df,table_name='yuliu_stock_source')
    
    def get_save_awd_stock_data_lingxing(self):
        """AWD库存领星"""
        storehouse_id = self.gssd.get_storehouse_id_data()
        data = self.gssd.get_awd_stock_data_lingxing(storehouse_id)
        if data:
            print('AWD库存领星获取成功')
            df = pd.DataFrame(data)
            json_columns = ['category_Arr', 'asin_principal_list', 'attribute','afn_fulfillable_quantity_multi']
            df = self._preprocess_dataframe(df,json_columns)
            df['get_date'] = self.today_date
            # 保存至数据库
            save_df_to_mysql_stock_sales(df=df,table_name='awd_stock_source')
    
    def get_save_30day_sales_data(self,sid,start_date,end_date):
        """获取并保存30日销量数据

        Args:
            sid: 店铺id (所有店铺的所有站点)
            start_date: 开始日期
            end_date: 结束日期
        """
        data = self.gssd.get_30day_sales_data(sid,start_date,end_date)
        if data:
            print(f'{sid}-近30日销量获取成功')
            df = pd.DataFrame(data)
            df['sid'] = sid
            df['get_date'] = self.today_date
            # 保存至数据库
            save_df_to_mysql_stock_sales(df=df,table_name='sales_30day_detail')
    
    def get_save_fba_shipment_data(self,sid,start_date,end_date):
        """获取并保存FBA货件数据

        Args:
            sid: 店铺id (所有店铺的所有站点)
            start_date: 开始日期
            end_date: 结束日期
        """
        data = self.gssd.get_fba_shipment_data(sid,start_date,end_date)
        if data:
            print(f'{sid}-FBA货件获取成功')
            json_columns = ['item_list', 'ship_from_address', 'ship_to_address','tracking_number_list']
            df = self._preprocess_dataframe(pd.DataFrame(data),json_columns)
            # 处理时间列的空值
            datetime_columns = ['gmt_modified', 'gmt_create', 'sync_time', 'working_time', 
                                'shipped_time', 'receiving_time', 'closed_time', 
                                'sta_shipment_date', 'sta_delivery_start_date', 'sta_delivery_end_date']
            for col in datetime_columns:
                if col in df.columns:
                    df[col] = df[col].replace('', None)
            df['get_date'] = self.today_date
            df.rename(columns={'id':'lingxing_id'},inplace=True)
            # 保存至数据库
            save_df_to_mysql_stock_sales(df=df,table_name='fba_shipment')
    
    def _preprocess_dataframe(self, df,json_columns:list):
        """
        预处理DataFrame,确保数据类型正确且没有特殊字符
        """
        for column in df.columns:
            # 处理列表/JSON类型的列
            if column in json_columns:
                df[column] = df[column].apply(lambda x: json.dumps(x) if x else '[]')
                continue
            # 处理数值型列
            if df[column].dtype in ['float64', 'float32']:
                df[column] = df[column].fillna(0.0)
                # 确保数值型数据不会超出数据库字段限制
                df[column] = df[column].apply(lambda x: round(float(x), 2) if pd.notnull(x) else 0.0)
            
            # 处理字符串列
            elif df[column].dtype == 'object':
                # 替换NULL为空字符串
                df[column] = df[column].fillna('')
                # 处理字符串中的特殊字符
                df[column] = df[column].astype(str).apply(lambda x: x.replace("'", "''"))
                # 截断过长的字符串（根据数据库字段长度限制调整）
                df[column] = df[column].str.slice(0, 255)
            
            # 处理整数列
            elif df[column].dtype in ['int64', 'int32']:
                df[column] = df[column].fillna(0)
                df[column] = df[column].astype(int)

        return df
    
    def get_save_all_store_stock_sales(self):
        """获取并保存所有店铺的库存以及销量"""
        self.get_save_awd_stock_data_lingxing()
        for idx ,row in self.store_id_df.iterrows():
            sid = row['sid']
            country = row['country']
            if country in ('美国','英国','德国'):
                self.get_save_fba_stock_data_source(country=country,sid=sid)  # FBA库存源表
                self.get_save_fba_stock_data_lingxing(country=country,sid=sid)  # FBA库存领星表
                self.get_save_yuliu_stock_data_source(country=country,sid=sid)  # 预留库存源表
        
            # 销量数据需要从所有店铺所有站点去拿
            self.get_save_30day_sales_data(sid,start_date=self.ago_30day,end_date=self.today_date)
    
    def get_save_all_store_fba_shipment(self,start_date,end_date):
        """获取FBA货件信息  只需在发货计划完成之后拿
        """
        for idx ,row in self.store_id_df.iterrows():
            sid = row['sid']
            country = row['country']
            if country in ('美国','英国','德国'):
                self.get_save_fba_shipment_data(sid,start_date,end_date)


def run_all_stock_sales():
    """ 获取并保存所有库存和销量
    每天取
    """
    psd = ProcessSaveData()
    psd.get_save_all_store_stock_sales()

def run_all_fba_shipment(start_date,end_date):
    """ 获取并保存所有FBA货件
    发货计划完成之后取
    """
    psd = ProcessSaveData()
    psd.get_save_all_store_fba_shipment(start_date,end_date)


if __name__ == '__main__':
    run_all_stock_sales()

    # # FBA货件信息
    # start_date = "2025-03-11"
    # end_date = "2025-03-13"
    # run_all_fba_shipment(start_date,end_date)






